<?php

/**
 * Base class for work with Database
 *  
 * @package Library
 * @subpackage Db_Table
 * @category Database modules
 */

/**
 * Connects the main class for database work.
 */
require_once(dirname(__FILE__)."/db.php");

/**
 * This class serves for working with a DB table.
 *
 */
class Db_Table {
    /**
     * Local values.
     */
    public $db = null;
    public $table = "";
    public $id = 'id';
    public $fields = array();
    public $fieldTypes = array();

    /**
     * get
     * Returns a class for working with a DB table.
     *
     * @param string $tblName
     * @access public    
     * @return Db_Table
     */
    static public function get($tblName, $conf = null)
    {
        $name = str_replace(array('-','_','.'),' ', strtolower($tblName));
        $name = preg_replace("[^a-z0-9 ]",'',$name);
        $name = str_replace(' ','',ucwords($name))."Model";
        if (class_exists($name))
            return new $name();
        return new Db_Table($tblName, $conf);
    }
    
    /**
     * __construct
     * Construct function for working with a DB table.
     *
     * @param string $table
     * @param class $conf
     */
    protected function __construct($table, $conf = null){
        if ($conf == null)
            $conf = _cfg("db");
        $this->table = $table;
        $this->db = Db::get($conf);     
        $this->fieldTypes = $this->db->getHash("SHOW COLUMNS FROM {$this->table}");
        foreach ($this->fieldTypes as $name => $type)
            $this->fields[$name] = $name;
        $this->id = key($this->fields);
    }
    
	/**
	 * _joinFields
     * Prepares list of fields for select SQL query.
     *      
     * @param  array $map
     * @return string
     */
    public function _joinFields($map = null)
    {
        $pairs = array();
        if (!$map)
            $map = $this->fields;
        foreach ($map as $alias => $field)
            $pairs[] = "$field AS $alias";
        return join(',', $pairs);           
    }
    
    /** 
     * _parseWhere
     * Prepares 'where' statement for sql query.
     * 
     * @param array $cond conditions
     * @param array $map mapping like array('name'=>'p.product_name', 'price'=>'p2o.price')
     * @return string where ststement like 1 AND p.product_name LIKE "zz%" AND p2o.price < 12
     */
    function _parseWhere($cond, $map)
    {
        if (!is_array($cond))
        {
            if (strlen($cond) == 0)
                return " 1=1 ";
            $cond = str_replace("="," = ",$cond);
            foreach ($map as $alias => $field)
            {
                $cond = str_replace(" {$alias} ", " {$field} ", " ".$this->db->escape($cond)." ");
            }
            return $cond;
        }
        $pairs = array('1');        
        foreach ($cond as $k => $expr)
        {
            if (is_numeric($k))
                $pairs[] = $expr;
            elseif (isset($map[$k]))
                $pairs[] = $map[$k].' '.$expr;
        }
        return join(' AND ', $pairs);
    }
    
    /**
     * escape
     * This function escapes the value of the field.
     *
     * @param string $field
     * @param string $value
     * @return string
     */
    public function _escape($field, $value)
    {
        if (strpos('int', $this->fieldTypes[$field]) > -1)
            return intval($value);
        return $this->db->escape($value);
    }
    
    /**
     * find
     * This function returns a record by ID or by other field.
     *
     * @param integer $id
     * @param string $field
     * @return array|hash
     */
    public function find($id, $field = null)
    {
        if (!$field)
            $field = $this->id;
        $sql = " SELECT ".$this->_joinFields()." FROM {$this->table} WHERE {$this->fields[$field]}='".$this->_escape($field, $id)."'";
        return $this->db->getRow($sql);
    }
    
    /**
     * getHash
     * This function returns hash in the format 'id=>title' accordinging to an SQL query.
     *
     * @param string $nameField
     * @param array $where
     * @return array
     */
    public function getHash($nameField = 'title', $where = array())
    {
        $sql = "SELECT {$this->id}, {$nameField}
                    FROM {$this->table}
                    WHERE ".$this->_parseWhere($where, $this->fields)." 
                    ORDER BY 2";
        return $this->db->getHash($sql);
    }
    
	/**
	 * getEmpty
	 * This function returns an empty record.
	 *
	 * @return array|hash
	 */
    public function getEmpty()
    {
        $res = array();
        foreach ($this->fields as $key => $f){
            $res[$key] = "";
        }
        return $res;
    }
    
    /**
     * getIdFieldName
     * This function returns the name of the field with a primary key.
     *
     * @return string
     */
    public function getIdFieldName()
    {
        return $this->id;
    }
    
    /**
     * setMap
     * This function sets an array of fields in a table.
     *
     * @param array $map
     * @return array
     */
    public function setMap($map){
        $this->fields = $map;
        $this->id = key($this->fields);
    }
    
    /**
     * getMap
     * This function returns an array of fields in a table.
     *
     * @return array
     */
    public function getMap()
    {       
        return $this->fields;
    }
    
    /** 
     * findBy
     * Selects a list of records from the DB table.
     * 
     * @param array|hash  $where   conditions (usually prepared by Filter class)
     * @param mixed  $sorter  instanse of Sorter object
     * @return array $rows  found rows
     */
    public function findBy($where=array(), $sort=array(), $offset = 0, $limit=10000){               
        $sql = "SELECT ".$this->_joinFields()." 
                FROM {$this->table} 
                WHERE ".$this->_parseWhere($where, $this->fields)." 
                ".$this->_parseOrder($sort)."
                LIMIT ".intVal($offset).",".intVal($limit)."";
        return $this->db->getRows($sql);
    }

    /** 
     * findByFull
     * Selects a list of records from the DB table with pager.
     * 
     * @param array $where   conditions (usually prepared by Filter class)
     * @param int   $pager   instanse of Pager object
     * @param int   $sorter  instanse of Sorter object
     * @return array $rows  found rows
     */
    function findByFull($where, $pager, $sorter)
    {
        $map = $this->fields;

        $where = $this->_parseWhere($where, $map);

        $sql = 'SELECT COUNT(*) FROM ' . $this->table . ' WHERE ' . $where;
        $cnt = $this->db->getField($sql);
        if (!$cnt)
            return array();

        if (is_object($pager))
            $limit = ($pager ? $pager->init($cnt) : '');
        else
            $limit = ($pager ? $pager : '');

        if (is_object($sorter))
            $order = ($sorter ? $sorter->init(array_keys($map)): '');
        else
            $order = ($sorter ? $sorter : '');
            
        $sql = 'SELECT ' . $this->_joinFields($map)
             .  ' FROM ' . $this->table
             .  ' WHERE ' . $where;
        if ($order)
            $sql .= ' ORDER BY ' . $order;
        if ($limit)
            $sql .= ' LIMIT ' . $limit;
        $rows = $this->db->getRows($sql);

        return $rows;
    }

    /**
     * countBy
     * This function returns the number of records in the table according to set conditions.
     *
     * @param array $where
     * @return integer
     */
    public function countBy($where=array()){
        $sql = "SELECT count(*)
                FROM {$this->table} 
                WHERE ".$this->_parseWhere($where, $this->fields);
        return intVal($this->db->getField($sql));
    }
    
	/**
	 * _parseOrder
	 * This function returns sorting conditions.
	 *
	 * @param array $order
	 * @return string
	 */
    public function _parseOrder($order)
    {
        if (is_array($order) && sizeof($order) > 0)
            return "ORDER BY ".join(',',$order);
        return "";
    }
    
    /**
	 * _filterData
	 * This function returns filter conditions.
	 *
	 * @param array $data
	 * @return string
	 */
    public function _filterData(&$data)
    {
        foreach ($this->fields as $alias => $field)
            if (isset($data[$alias]))
                $data[$field] = $this->db->escape($data[$alias]);
        foreach ($data as $key => $value)
            if (!in_array($key, $this->fields))
                unset($data[$key]);
    }
    
    /**
     * changeMapItem
     * This function sets a value for a given field.
     *
     * @param string $key
     */
    public function changeMapItem($key, $value)
    {
        if (isset($this->fields[$key]))
            $this->fields[$key] = $value;
    }
    
    /**
     * update
     * This function updates a record by ID. 
     *
     * @param array $data
     * @param integer $id
     * @return boolean
     */
    public function update($data, $id)
    {
        $this->_filterData($data);
        return $this->db->update($this->table, $data, $this->fields[$this->id]."='$id'");
    }

    /**
     * updateBy
     * This function updates the records according to the given conditions. 
     *
     * @param array $data
     * @param array $where
     * @return boolean
     */
    public function updateBy($data, $where=array())
    {
        $this->_filterData($data);
        return $this->db->update($this->table, $data, $this->_parseWhere($where, $this->fields));
    }
    
    /**
     * insert
     * This function inserts a record. 
     *
     * @param array $data
     * @param boolean $escape
     * @param boolean $unsetId
     * @return boolean
     */
    public function insert($data, $escape = false, $unsetId = true)
    {
        $this->_filterData($data);
        if ($unsetId)
	        unset($data[$this->id]);
        return $this->db->insert($this->table, $data, $escape);
    }

    /**
     * delete
     * This function deletes a record from the DB table by ID.
     *
     * @param integer $id
     * @return boolean
     */
    public function delete($id)
    { 
        $sql = "DELETE FROM `{$this->table}` WHERE `{$this->fields[$this->id]}` = '".intval($id)."'";
        return $this->db->query($sql);
    }

    /**
     * deleteBy
     * This function deletes a record from the DB table by condition.
     *
     * @param array $where
     * @return boolean
     */
    public function deleteBy($where=array())
    {
        $sql = "DELETE FROM `{$this->table}` WHERE " . $this->_parseWhere($where, $this->fields);
        return $this->db->query($sql);
    }
}